#include "foodsql.h"
#include <QMessageBox>
#include <QCoreApplication>
#include <QSqlQuery>
#include <QtDebug>
#include <QSqlError>
foodSql * foodSql::ptrfoodSql = nullptr;
foodSql::foodSql(QObject *parent)
    : QObject{parent}
{


    init();

    //q.exec("INSERT INTO food VALUES (NUll,'白菜',2,1,'蔬菜','2022-1-20')");
    //qDebug()<<getFoodCut();
    //getPageFood(2,3);
    //delFood(14);

    //FoodInfo f;
    //f.id=7;
    //f.name="鸡蛋";
    //updateFoodInfo(f);
}

void foodSql::init()
{
    if (QSqlDatabase::drivers().isEmpty())
        qDebug()<<"No database drivers found";

    m_db = QSqlDatabase::addDatabase("QSQLITE");
#if 1
    auto str = QCoreApplication::applicationDirPath()+"/data.db";
    qDebug()<<str;
#endif
    //str = "F:\\data.db"
    m_db.setDatabaseName(str);//设置数据库路径
    if(!m_db.open())
        qDebug()<<"db not open";
}

quint32 foodSql::getFoodCut()
{
    QSqlQuery sql(m_db);
    sql.exec("select count(id) from food;");
    quint32 uiCnt = 0;
    while(sql.next())
    {
        uiCnt = sql.value(0).toUInt();
    }
    return uiCnt;
}

QList<FoodInfo> foodSql::getPageFood(quint32 page, quint32 uiCnt)
{
    QList<FoodInfo> l;
    QSqlQuery sql(m_db);
    QString strsql = QString("select * from food order by id limit %1 offset %2").
            arg(uiCnt).arg(page*uiCnt);
    sql.exec(strsql);

    FoodInfo info;
    while(sql.next())
    {
        info.id=sql.value(0).toUInt();
        info.name=sql.value(1).toString();
        info.num=sql.value(2).toUInt();
        info.foodid=sql.value(3).toUInt();
        info.foodunit=sql.value(4).toString();
        info.date=sql.value(5).toString();
        l.push_back(info);
    }
    return l;
}

bool foodSql::addFood(FoodInfo foodInfo)
{
    QSqlQuery sql(m_db);
    QString strSql = QString("insert into food values(null,'%1',%2,%3,'%4','%5')").
            arg(foodInfo.name).arg(foodInfo.num).arg(foodInfo.foodid).arg(foodInfo.foodunit).arg(foodInfo.date);
    return sql.exec(strSql);

}

bool foodSql::delFood(int id)
{
    QSqlQuery sql(m_db);
    return sql.exec(QString("delete from food where id = %1").arg(id));
}

void foodSql::clearFoodTable()
{
    QSqlQuery sql(m_db);
    sql.exec("delete from food");
    sql.exec("delete from sqlite_sequence where name ='food'");
}

void foodSql::updateFoodInfo(FoodInfo foodInfo)
{
    QSqlQuery sql(m_db);
    QString strSql = QString("update food set name = '%1', num = %2, foodid = %3, foodunit = '%4', date = '%5' where id = %6").
            arg(foodInfo.name).arg(foodInfo.num).arg(foodInfo.foodid).arg(foodInfo.foodunit).arg(foodInfo.date).arg(foodInfo.id);

    qDebug()<<strSql;
    qDebug()<<sql.exec(strSql);
    QSqlError e = sql.lastError();
    if(e.isValid())
    {
        qDebug()<<e.text();
    }
}
